package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.PracticeSubjectsType;
import com.xmy.cultivate.entity.response.PracticePlanStaff;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 科目类型 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-10-09
 */
@Mapper
public interface PracticeSubjectsTypeMapper extends BaseMapper<PracticeSubjectsType> {

    @Select("SELECT t1.*,t2.type,t2.`name` FROM practice_subjects_type AS t1 INNER JOIN practice_type AS t2 ON t1.practice_type_id = t2.id AND t2.deleted = 0 ${ew.customSqlSegment} ")
    public abstract List<PracticeSubjectsType> getPracticeSubjectsTypeList(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 完成率列表
     */
    @Select({"<script>",
            "SELECT t.teacher_id,t.year_class_id,t.subjects_id,t.class_type_id,t.year_part,t.quarter_num,t.subjects_name,t.year_class_name,t.lesson_num,",
            "t.teacher_name,t.finish_num,t.option_value,t.score,t.plan_add_date,",
            "<if test= 'type == 6'>",
                "t01.school_id,getSchoolName(t01.school_id)AS school_name,t01.pid AS school_pid,",
                "SUM( CASE WHEN t.id IS NOT NULL ",
                    "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                        "AND year_class_id IN (${yearClassId}) ",
                    "</when>",
                    "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                        "AND class_type_id IN (${classTypeId}) ",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                        "AND subjects_id = ${subjectsId} ",
                    "</when>",
                    "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                        "AND t.teacher_name LIKE '%${teacherName}%' ",
                    "</when>",
                "THEN 1 ELSE 0 END ) AS show_pass_num, ",

                "SUM( CASE WHEN finish_num > 0 ",
                    "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                        "AND year_class_id IN (${yearClassId}) ",
                    "</when>",
                    "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                        "AND class_type_id IN (${classTypeId}) ",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                        "AND subjects_id = ${subjectsId} ",
                    "</when>",
                    "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                        "AND t.teacher_name LIKE '%${teacherName}%' ",
                    "</when>",
                "THEN finish_num ELSE 0 END ) AS yet_pass_num, ",

                "SUM( CASE WHEN finish_num = 0 ",
                    "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                        "AND year_class_id IN (${yearClassId}) ",
                    "</when>",
                    "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                        "AND class_type_id IN (${classTypeId}) ",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                        "AND subjects_id = ${subjectsId} ",
                    "</when>",
                    "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                        "AND t.teacher_name LIKE '%${teacherName}%' ",
                    "</when>",
                "THEN 1 ELSE 0 END ) AS not_pass_num, ",

                "SUM( CASE WHEN finish_num > 0 AND plan_add_date BETWEEN '${startDate}' AND '${endDate}' ",
                    "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                        "AND year_class_id IN (${yearClassId}) ",
                    "</when>",
                    "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                        "AND class_type_id IN (${classTypeId}) ",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                        "AND subjects_id = ${subjectsId} ",
                    "</when>",
                    "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                        "AND t.teacher_name LIKE '%${teacherName}%' ",
                    "</when>",
                "THEN finish_num ELSE 0 END ) AS date_pass_num ",
            "</if>",

            "<if test= 'type != 6'>",
                "t.school_id,getSchoolName(t.school_id)AS school_name,",
                "SUM( CASE WHEN id IS NOT NULL THEN 1 ELSE 0 END ) AS show_pass_num, ",
                "SUM( CASE WHEN finish_num > 0 THEN finish_num ELSE 0 END ) AS yet_pass_num, ",
                "SUM( CASE WHEN finish_num = 0 THEN 1 ELSE 0 END ) AS not_pass_num, ",
                "SUM( CASE WHEN finish_num = 1 THEN score ELSE 0 END ) AS all_score, ",
                "SUM( CASE WHEN finish_num = 1 AND plan_add_date BETWEEN '${startDate}' AND '${endDate}' THEN score ELSE 0 END ) AS date_score, ",
                "SUM( CASE WHEN finish_num > 0 AND plan_add_date BETWEEN '${startDate}' AND '${endDate}' THEN finish_num ELSE 0 END ) AS date_pass_num ",
            "</if>",

            "<if test= 'type == 6'>",
            "FROM ",
            "( SELECT * FROM school_quarter WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND deleted = 0) AS t01 ",
            "LEFT JOIN (",
            "</if>",
            "<if test= 'type != 6'>",
            "FROM ( ",
            "</if>",

            "SELECT ",
            "t01.*,",
            "getSubjectsName ( t01.subjects_id ) AS subjects_name, ",
            "getDictName ( t01.year_class_id, 14 ) AS year_class_name, ",
            "t02.lesson_num, ",
            "IFNULL( t03.finish_num, 0 ) AS finish_num, ",
            "t03.option_value, ",
            "IFNULL(t03.score,0) as score, ",
            "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date ",
            "FROM ( ",
                "SELECT ",
                "t1.id,t1.school_id,t1.teacher_id,t1.year_class_id,t1.subjects_id,",
                 "t1.course_type_id AS class_type_id,",
                 "t1.year_part,t1.quarter_num, ",
                "t1.status,",
                "t1.finish_date,",
                "DATE_FORMAT( t1.created_at, '%Y-%m-%d' ) AS grade_created_at, ",
                "t2.NAME AS teacher_name, ",
                "(SELECT count( id ) FROM student_grade AS stu_g WHERE stu_g.grade_id = t1.id AND stu_g.is_last = 1 AND ((stu_g.reading_status = 1 ) OR ( stu_g.reading_status = 5 AND stu_g.use_course_count > 0 ))) AS stu_grade_count,",
                "1 AS teacher_type ",
                "FROM ",
                "grade AS t1 ",
                "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id AND t1.lesson_type = ${lessonType}",
                "WHERE ",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum} ",
                "AND t1.deleted = 0 ",
                "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t1.school_id IN (${schoolId}) ",
                "</when>",
                "<if test= 'type != 6'>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND t1.year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                "AND t1.course_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t1.subjects_id IN (${subjectsId}) ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                "AND t2.`name` LIKE '%${teacherName}%' ",
                "</when>",
                "</if>",
                "<if test= 'type == 3'>",
                "AND t2.id IN (select admin_id from role_admin where role_id = 1568160887369994242) ",
                "</if>",
                "GROUP BY ",
                "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id,teacher_type",
                "<if test= 'type == 2 or type == 4 or type == 6'>",
                ",t1.school_id",
                "</if>",
                "UNION ALL",
                "SELECT",
                      "t1.id,",
                      "t1.school_id,",
                      "t1.teacher_id,",
                      "t1.year_class_id,",
                      "t1.subjects_id,",
                      "t1.course_type_id AS class_type_id,",
                      "t1.year_part,",
                      "t1.quarter_num,",
                      "1 as `status`,",
                      "NULL AS finish_date,",
                      "NULL AS grade_created_at,",
                      "t2.NAME AS teacher_name,",
                      "NULL AS stu_grade_count,",
                      "t1.teacher_type",
                    "FROM",
                      "practice_brush_teacher AS t1",
                      "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.deleted = 0 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t1.school_id IN (${schoolId}) ",
            "</when>",
            "<if test= 'type != 6'>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND t1.year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                    "AND t1.course_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND t1.subjects_id IN (${subjectsId}) ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                    "AND t2.`name` LIKE '%${teacherName}%' ",
                "</when>",
            "</if>",
            "<if test= 'type == 3'>",
                "AND t2.id IN (select admin_id from role_admin where role_id = 1568160887369994242) ",
            "</if>",
            "GROUP BY ",
            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id,teacher_type,t1.teacher_type",
            "<if test= 'type == 2 or type == 4 or type == 6'>",
            ",t1.school_id",
            "</if>",
            ") AS t01 ",
            "LEFT JOIN rade_exclude_teacher AS t08 ON t01.year_part = t08.year_part  AND t01.quarter_num = t08.quarter_num AND t01.teacher_id = t08.teacher_id AND t08.type = 1",
            "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t02.deleted = 0 ",
            "AND (( t01.teacher_type = 1 AND t01.grade_created_at &lt;= t02.task_start_date AND t01.stu_grade_count > 0 ) OR ( t01.teacher_type = 2 ) or (t01.teacher_type = 1 and stu_grade_count is null)) AND (t01.STATUS &lt;&gt; 2 OR (t01.STATUS = 2 AND ( t01.finish_date &gt;= t02.task_start_date ))) ",
            "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd} ",
            "INNER JOIN practice_subjects_type AS t05 ON t01.year_part = t05.year_part AND t01.quarter_num = t05.quarter_num AND t01.subjects_id = t05.subjects_id AND t05.deleted = 0",
            "INNER JOIN practice_type AS t04 ON t04.id = t05.practice_type_id  AND t04.teacher_type = t01.teacher_type",
            "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
                "AND t04.id = ${practiceTypeId}",
            "</when>",
            "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "AND t01.year_class_id = t03.year_class_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t02.lesson_num = t03.lesson_num ",
            "AND t01.class_type_id = t03.class_type_id ",
            "AND t03.deleted = t02.deleted ",
            "AND t01.teacher_id = t03.teacher_id ",
            "AND t03.practice_type_id = t04.id ",
            "and t03.practice_type_id = t05.practice_type_id",
            "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
            "AND t03.practice_type_id = ${practiceTypeId} ",
            "</when>",
            "WHERE t08.id IS NULL ",
            ") AS t ",
            "<if test= 'type == 6'>",
                "ON t01.school_id = t.school_id ",
            "</if>",
            "WHERE 1=1",
            "<when test='trackId!=null and trackId!=&apos;&apos;'>",
            "AND t.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id IN (#{trackId}) )",
            "</when>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
                "teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
                "school_id,teacher_id",
            "</if>",
            "<if test= 'type == 4 or type == 6'>",
                "t.school_id",
            "</if>",
            "</script>"})
    public abstract List<PracticePlanStaff> practicePlanStaffList(@Param("type") String type, @Param("practiceTypeId") String practiceTypeId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("lessonNumStart") String lessonNumStart, @Param("lessonNumEnd") String lessonNumEnd, @Param("lessonType") String lessonType, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("teacherName") String teacherName, @Param("teacherType") Integer teacherType, @Param("trackId") String trackId);


    /**
     * 教学积分
     * @param type
     * @param practiceTypeId
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param startDate
     * @param endDate
     * @param lessonNumStart
     * @param lessonNumEnd
     * @param lessonType
     * @param schoolId
     * @param yearClassId
     * @param classTypeId
     * @param teacherName
     * @return
     */
    @Select({"<script>",
            "SELECT",
                      "t.teacher_id,",
                      "t.year_class_id,",
                      "t.subjects_id,",
                      "t.class_type_id,",
                      "t.year_part,",
                      "t.quarter_num,",
                      "t.subjects_name,",
                      "t.year_class_name,",
                      "t.lesson_num,",
                      "t.teacher_name,",
                      "t.finish_num,",
                      "t.option_value,",
                      "t.score,",
                      "t.plan_add_date,",
                        "t.school_id,getSchoolName(t.school_id)AS school_name,",
                        "SUM( CASE WHEN id IS NOT NULL THEN 1 ELSE 0 END ) AS show_pass_num, ",
                        "SUM( CASE WHEN finish_num > 0 THEN finish_num ELSE 0 END ) AS yet_pass_num, ",
                        "SUM( CASE WHEN finish_num = 0 THEN 1 ELSE 0 END ) AS not_pass_num, ",
                        "SUM( CASE WHEN finish_num = 1 THEN score ELSE 0 END ) AS all_score, ",
                        "SUM( CASE WHEN finish_num = 1 AND plan_add_date BETWEEN '${startDate}' AND '${endDate}' THEN score ELSE 0 END ) AS date_score, ",
                        "SUM( CASE WHEN finish_num > 0 AND plan_add_date BETWEEN '${startDate}' AND '${endDate}' THEN finish_num ELSE 0 END ) AS date_pass_num ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t01.*,",
                        "getSubjectsName ( t01.subjects_id ) AS subjects_name,",
                        "getDictName ( t01.year_class_id, 14 ) AS year_class_name,",
                        "t02.lesson_num,",
                        "IFNULL( t03.finish_num, 0 ) AS finish_num,",
                        "t03.option_value,",
                        "IFNULL( t03.score, 0 ) AS score,",
                        "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date ",
                      "FROM",
                        "(",
                        "SELECT",
                          "t1.id,",
                          "t1.school_id,",
                          "t1.teacher_id,",
                          "t1.year_class_id,",
                          "t1.subjects_id,",
                          "t1.course_type_id AS class_type_id,",
                          "t1.year_part,",
                          "t1.quarter_num,",
                          "t1.STATUS,",
                          "t1.finish_date,",
                          "DATE_FORMAT( t1.created_at, '%Y-%m-%d' ) AS grade_created_at,",
                          "t2.NAME AS teacher_name,",
                          "(SELECT count( id ) FROM student_grade AS stu_g WHERE stu_g.grade_id = t1.id AND stu_g.is_last = 1 AND ((stu_g.reading_status = 1 ) OR ( stu_g.reading_status = 5 AND stu_g.use_course_count > 0 ))) AS stu_grade_count,",
                          "1 AS teacher_type ",
                        "FROM",
                          "grade AS t1",
                          "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                          "AND t1.lesson_type = 1 ",
                        "WHERE",
                            "t1.year_part = ${yearPart} ",
                            "AND t1.quarter_num = ${quarterNum} ",
                            "AND t1.deleted = 0 ",
                            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                            "AND t1.school_id IN (${schoolId}) ",
                            "</when>",
                            "<if test= 'type != 6'>",
                            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                            "AND t1.year_class_id IN (${yearClassId}) ",
                            "</when>",
                            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                            "AND t1.course_type_id IN (${classTypeId}) ",
                            "</when>",
                            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                            "AND t1.subjects_id IN (${subjectsId}) ",
                            "</when>",
                            "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                            "AND t2.`name` LIKE '%${teacherName}%' ",
                            "</when>",
                            "</if>",
                            "<if test= 'type == 3'>",
                            "AND t2.id IN (select admin_id from role_admin where role_id = 1568160887369994242) ",
                            "</if>",
                            "GROUP BY ",
                            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id",
                            "<if test= 'type == 2 or type == 4 or type == 6'>",
                            ",t1.school_id",
                            "</if>",
                        "UNION ALL",
                        "SELECT",
                          "t1.id,",
                          "t1.school_id,",
                          "t1.teacher_id,",
                          "t1.year_class_id,",
                          "t1.subjects_id,",
                          "t1.course_type_id AS class_type_id,",
                          "t1.year_part,",
                          "t1.quarter_num,",
                          "1 as `status`,",
                          "NULL AS finish_date,",
                          "NULL AS grade_created_at,",
                          "t2.NAME AS teacher_name,",
                          "NULL AS stu_grade_count,",
                          "t1.teacher_type ",
                        "FROM",
                          "practice_brush_teacher AS t1",
                          "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                        "WHERE",
                            "t1.year_part = ${yearPart} ",
                            "AND t1.quarter_num = ${quarterNum} ",
                            "AND t1.deleted = 0 ",
                            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                            "AND t1.school_id IN (${schoolId}) ",
                            "</when>",
                            "<if test= 'type != 6'>",
                            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                            "AND t1.year_class_id IN (${yearClassId}) ",
                            "</when>",
                            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                            "AND t1.course_type_id IN (${classTypeId}) ",
                            "</when>",
                            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                            "AND t1.subjects_id IN (${subjectsId}) ",
                            "</when>",
                            "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                            "AND t2.`name` LIKE '%${teacherName}%' ",
                            "</when>",
                            "</if>",
                            "<if test= 'type == 3'>",
                            "AND t2.id IN (select admin_id from role_admin where role_id = 1568160887369994242) ",
                            "</if>",
                            "GROUP BY ",
                            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id,t1.teacher_type",
                            "<if test= 'type == 2 or type == 4 or type == 6'>",
                            ",t1.school_id",
                            "</if>",
                        ") AS t01",
                        "LEFT JOIN rade_exclude_teacher AS t08 ON t01.year_part = t08.year_part  AND t01.quarter_num = t08.quarter_num AND t01.teacher_id = t08.teacher_id AND t08.type = 1",
                        "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
                        "AND t01.quarter_num = t02.quarter_num ",
                        "AND t02.deleted = 0 ",
                        "AND (( t01.teacher_type = 1 AND t01.grade_created_at &lt;= t02.task_start_date AND t01.stu_grade_count > 0 ) OR ( t01.teacher_type = 2 ) or (t01.teacher_type = 1 and stu_grade_count is null)) AND (t01.STATUS &lt;&gt; 2 OR (t01.STATUS = 2 AND ( t01.finish_date &gt;= t02.task_start_date ))) ",
                        "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd} ",
                        "INNER JOIN practice_subjects_type AS t05 ON t01.year_part = t05.year_part AND t01.quarter_num = t05.quarter_num AND t01.subjects_id = t05.subjects_id AND t05.deleted = 0",
                        "INNER JOIN practice_type AS t04 ON t04.id = t05.practice_type_id  AND t04.teacher_type = t01.teacher_type",
                        "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
                        "AND t04.id = ${practiceTypeId}",
                        "</when>",
                        "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
                        "AND t01.quarter_num = t03.quarter_num ",
                        "AND t01.year_class_id = t03.year_class_id ",
                        "AND t01.subjects_id = t03.subjects_id ",
                        "AND t02.lesson_num = t03.lesson_num ",
                        "AND t01.class_type_id = t03.class_type_id ",
                        "AND t03.deleted = t02.deleted ",
                        "AND t01.teacher_id = t03.teacher_id ",
                        "AND t03.practice_type_id = t04.id ",
                        "and t03.practice_type_id = t05.practice_type_id",
                        "WHERE t08.id IS NULL ",
                      ") AS t ",
                    "GROUP BY",
                      "teacher_id",
            "</script>"})
    public abstract List<PracticePlanStaff> practicePlanStaffAllList(@Param("type") String type, @Param("practiceTypeId") String practiceTypeId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("lessonNumStart") String lessonNumStart, @Param("lessonNumEnd") String lessonNumEnd, @Param("lessonType") String lessonType, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("teacherName") String teacherName);


    @Select({"<script>",
                      "SELECT",
                        "t01.*,",
                        "getCourseTypeName(t01.class_type_id) as class_type_name,",
                        "getSubjectsName ( t01.subjects_id ) AS subjects_name,",
                        "getDictName ( t01.year_class_id, 14 ) AS year_class_name,",
                        "t02.lesson_num,",
                        "IFNULL( t03.finish_num, 0 ) AS finish_num,",
                        "t03.option_value,",
                        "IFNULL( t03.score, 0 ) AS score,",
                        "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date ",
                      "FROM",
                        "(",
                        "SELECT",
                          "t1.id,",
                          "t1.school_id,",
                          "t1.teacher_id,",
                          "t1.year_class_id,",
                          "t1.subjects_id,",
                          "t1.course_type_id AS class_type_id,",
                          "t1.year_part,",
                          "t1.quarter_num,",
                          "t1.STATUS,",
                          "t1.finish_date,",
                          "DATE_FORMAT( t1.created_at, '%Y-%m-%d' ) AS grade_created_at,",
                          "t2.NAME AS teacher_name,",
                          "(SELECT count( id ) FROM student_grade AS stu_g WHERE stu_g.grade_id = t1.id AND stu_g.is_last = 1 AND ((stu_g.reading_status = 1 ) OR ( stu_g.reading_status = 5 AND stu_g.use_course_count > 0 ))) AS stu_grade_count,",
                          "1 AS teacher_type ",
                        "FROM",
                          "grade AS t1",
                          "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                          "AND t1.lesson_type = 1 ",
                        "WHERE",
                            "t1.year_part = ${yearPart} ",
                            "AND t1.quarter_num = ${quarterNum} ",
                            "AND t1.deleted = 0 ",
                            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                            "AND t1.school_id IN (${schoolId}) ",
                            "</when>",
                            "<if test= 'type != 6'>",
                            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                            "AND t1.year_class_id IN (${yearClassId}) ",
                            "</when>",
                            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                            "AND t1.course_type_id IN (${classTypeId}) ",
                            "</when>",
                            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                            "AND t1.subjects_id IN (${subjectsId}) ",
                            "</when>",
                            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                            "AND t2.`id` = ${teacherId} ",
                            "</when>",
                            "</if>",
                            "<if test= 'type == 3'>",
                            "AND t2.id IN (select admin_id from role_admin where role_id = 1568160887369994242) ",
                            "</if>",
                            "GROUP BY ",
                            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id",
                            "<if test= 'type == 2 or type == 4 or type == 6'>",
                            ",t1.school_id",
                            "</if>",
                        "UNION ALL",
                        "SELECT",
                          "t1.id,",
                          "t1.school_id,",
                          "t1.teacher_id,",
                          "t1.year_class_id,",
                          "t1.subjects_id,",
                          "t1.course_type_id AS class_type_id,",
                          "t1.year_part,",
                          "t1.quarter_num,",
                          "1 as `status`,",
                          "NULL AS finish_date,",
                          "NULL AS grade_created_at,",
                          "t2.NAME AS teacher_name,",
                          "NULL AS stu_grade_count,",
                          "t1.teacher_type ",
                        "FROM",
                          "practice_brush_teacher AS t1",
                          "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
                        "WHERE",
                            "t1.year_part = ${yearPart} ",
                            "AND t1.quarter_num = ${quarterNum} ",
                            "AND t1.deleted = 0 ",
                            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                            "AND t1.school_id IN (${schoolId}) ",
                            "</when>",
                            "<if test= 'type != 6'>",
                            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                            "AND t1.year_class_id IN (${yearClassId}) ",
                            "</when>",
                            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                            "AND t1.course_type_id IN (${classTypeId}) ",
                            "</when>",
                            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                            "AND t1.subjects_id IN (${subjectsId}) ",
                            "</when>",
                            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                            "AND t2.`id` = ${teacherId} ",
                            "</when>",
                            "</if>",
                            "<if test= 'type == 3'>",
                            "AND t2.id IN (select admin_id from role_admin where role_id = 1568160887369994242) ",
                            "</if>",
                            "GROUP BY ",
                            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id,t1.teacher_type",
                            "<if test= 'type == 2 or type == 4 or type == 6'>",
                            ",t1.school_id",
                            "</if>",
                        ") AS t01",
                        "LEFT JOIN rade_exclude_teacher AS t08 ON t01.year_part = t08.year_part  AND t01.quarter_num = t08.quarter_num AND t01.teacher_id = t08.teacher_id AND t08.type = 1",
                        "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
                        "AND t01.quarter_num = t02.quarter_num ",
                        "AND t02.deleted = 0 ",
                        "AND (( t01.teacher_type = 1 AND t01.grade_created_at &lt;= t02.task_start_date AND t01.stu_grade_count > 0 ) OR ( t01.teacher_type = 2 ) or (t01.teacher_type = 1 and stu_grade_count is null)) AND (t01.STATUS &lt;&gt; 2 OR (t01.STATUS = 2 AND ( t01.finish_date &gt;= t02.task_start_date ))) ",
                        "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd} ",
                        "INNER JOIN practice_subjects_type AS t05 ON t01.year_part = t05.year_part AND t01.quarter_num = t05.quarter_num AND t01.subjects_id = t05.subjects_id AND t05.deleted = 0",
                        "INNER JOIN practice_type AS t04 ON t04.id = t05.practice_type_id  AND t04.teacher_type = t01.teacher_type",
                        "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
                        "AND t04.id = ${practiceTypeId}",
                        "</when>",
                        "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
                        "AND t01.quarter_num = t03.quarter_num ",
                        "AND t01.year_class_id = t03.year_class_id ",
                        "AND t01.subjects_id = t03.subjects_id ",
                        "AND t02.lesson_num = t03.lesson_num ",
                        "AND t01.class_type_id = t03.class_type_id ",
                        "AND t03.deleted = t02.deleted ",
                        "AND t01.teacher_id = t03.teacher_id ",
                        "AND t03.practice_type_id = t04.id ",
                        "and t03.practice_type_id = t05.practice_type_id",
                        "WHERE t08.id IS NULL ",
            "</script>"})
    public abstract IPage<PracticePlanStaff> practicePlanStaffAllDetail(IPage<?> page, @Param("type") String type, @Param("practiceTypeId") String practiceTypeId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("lessonNumStart") String lessonNumStart, @Param("lessonNumEnd") String lessonNumEnd, @Param("lessonType") String lessonType, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("teacherId") String teacherId);

    /**
     * 完成率战队列表
     */
    @Select({"<script>",
            "SELECT ",
            "t1.teacher_id,",
            "getStaffNameForId(t1.teacher_id) AS teacher_name,",
            "t.year_class_id,",
            "t.subjects_id,",
            "t.class_type_id,",
            "t.year_part,",
            "t.quarter_num,",
            "t.subjects_name,",
            "t.year_class_name,",
            "t.lesson_num,",
            "t.finish_num,",
            "t.option_value,",
            "t.score,",
            "t.plan_add_date,",
            "t.school_id,",
            "getSchoolName(t.school_id)AS school_name,",
            "team_id,team_pid,team_name,",
            "SUM( CASE WHEN t.id IS NOT NULL ",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "AND class_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND subjects_id = ${subjectsId} ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                    "AND t.teacher_name LIKE '%${teacherName}%' ",
                "</when>",
            "THEN 1 ELSE 0 END ) AS show_pass_num, ",

            "SUM( CASE WHEN finish_num > 0 ",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "AND class_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND subjects_id = ${subjectsId} ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                    "AND t.teacher_name LIKE '%${teacherName}%' ",
                "</when>",
            "THEN finish_num ELSE 0 END ) AS yet_pass_num, ",

            "SUM( CASE WHEN finish_num = 0 ",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "AND class_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND subjects_id = ${subjectsId} ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                    "AND t.teacher_name LIKE '%${teacherName}%' ",
                "</when>",
            "THEN 1 ELSE 0 END ) AS not_pass_num, ",

            "SUM( CASE WHEN finish_num = 1 ",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "AND class_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND subjects_id = ${subjectsId} ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                    "AND t.teacher_name LIKE '%${teacherName}%' ",
                "</when>",
            "THEN score ELSE 0 END ) AS all_score, ",

            "SUM( CASE WHEN finish_num = 1 AND plan_add_date BETWEEN '${startDate}' AND '${endDate}'  ",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "AND class_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND subjects_id = ${subjectsId} ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                    "AND t.teacher_name LIKE '%${teacherName}%' ",
                "</when>",
            "THEN score ELSE 0 END ) AS date_score,",

            "SUM( CASE WHEN finish_num > 0 AND plan_add_date BETWEEN '${startDate}' AND '${endDate}' ",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND class_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND subjects_id = ${subjectsId} ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                "AND t.teacher_name LIKE '%${teacherName}%' ",
                "</when>",
            "THEN finish_num ELSE 0 END ) AS date_pass_num, ",

            "COUNT(DISTINCT CASE WHEN t.teacher_id IS NOT NULL ",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND class_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND subjects_id = ${subjectsId} ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                "AND t.teacher_name LIKE '%${teacherName}%' ",
                "</when>",
            "THEN t.teacher_id ELSE NULL END) AS teacher_count ",

            "FROM",
            "( ",
            "SELECT ",
            "td.*,td.sourse_id as teacher_id ,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_market AS tm ",
            "LEFT JOIN track_detail AS td ON tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
                "and tm.id IN (${teamId}) ",
            "</when>",

            "GROUP BY ",
            "tm.id,td.sourse_id ",
            ") AS t1 ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
                "INNER JOIN (",
            "</when>",
            "<when test='teamId==null or teamId==&apos;&apos; '>",
                "LEFT JOIN (",
            "</when>",
            "SELECT ",
            "t01.*,",
            "getSubjectsName ( t01.subjects_id ) AS subjects_name, ",
            "getDictName ( t01.year_class_id, 14 ) AS year_class_name, ",
            "t02.lesson_num, ",
            "IFNULL( t03.finish_num, 0 ) AS finish_num, ",
            "t03.option_value, ",
            "IFNULL(t03.score,0) as score, ",
            "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date ",
            "FROM ( ",
            "SELECT ",
            "t1.id,t1.school_id,t1.teacher_id,t1.year_class_id,t1.subjects_id,t1.course_type_id AS class_type_id,t1.year_part,t1.quarter_num, ",
            "t1.status,",
            "t1.finish_date,",
            "DATE_FORMAT( t1.created_at, '%Y-%m-%d' ) AS grade_created_at,t2.`name` AS teacher_name, ",
            "(SELECT count( id ) FROM student_grade AS stu_g WHERE stu_g.grade_id = t1.id AND stu_g.is_last = 1 AND ((stu_g.reading_status = 1 ) OR ( stu_g.reading_status = 5 AND stu_g.use_course_count > 0 ))) AS stu_grade_count,",
            "1 AS teacher_type ",
            "FROM ",
            "grade AS t1 ",
            "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id AND t1.lesson_type = ${lessonType}",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.deleted = 0 ",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND t1.year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND t1.course_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t1.subjects_id IN (${subjectsId}) ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                "AND t2.`name` LIKE '%${teacherName}%' ",
                "</when>",
            "</when>",
            "GROUP BY ",
            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id",
            "UNION ALL",
            "SELECT",
            "t1.id,",
            "t1.school_id,",
            "t1.teacher_id,",
            "t1.year_class_id,",
            "t1.subjects_id,",
            "t1.course_type_id AS class_type_id,",
            "t1.year_part,",
            "t1.quarter_num,",
            "1 as `status`,",
            "NULL AS finish_date,",
            "NULL AS grade_created_at,",
            "t2.NAME AS teacher_name,",
            "NULL AS stu_grade_count,",
            "t1.teacher_type ",
            "FROM",
            "practice_brush_teacher AS t1",
            "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.deleted = 0 ",
            "<when test='trackId!=null and trackId!=&apos;&apos;'>",
                "AND t2.id in(select sourse_id from track_detail where deleted = 0 and track_id IN (#{trackId})) ",
            "</when>",
            "<when test='teamId!=null and teamId!=&apos;&apos; '>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                    "AND t1.year_class_id IN (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "AND t1.course_type_id IN (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                    "AND t1.subjects_id IN (${subjectsId}) ",
                "</when>",
                "<when test='teacherName!=null and teacherName!=&apos;&apos; '>",
                    "AND t2.`name` LIKE '%${teacherName}%' ",
                "</when>",
            "</when>",
            "GROUP BY ",
            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id,t1.teacher_type",
            ") AS t01 ",
            "LEFT JOIN rade_exclude_teacher AS t08 ON t01.year_part = t08.year_part  AND t01.quarter_num = t08.quarter_num AND t01.teacher_id = t08.teacher_id AND t08.type = 1",
            "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t02.deleted = 0 ",
            "AND (( t01.teacher_type = 1 AND t01.grade_created_at &lt;= t02.task_start_date AND t01.stu_grade_count > 0 ) OR ( t01.teacher_type = 2 ) or (t01.teacher_type = 1 and stu_grade_count is null)) AND (t01.STATUS &lt;&gt; 2 OR (t01.STATUS = 2 AND ( t01.finish_date &gt;= t02.task_start_date ))) ",
            "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd} ",
            "INNER JOIN practice_subjects_type AS t05 ON t01.year_part = t05.year_part AND t01.quarter_num = t05.quarter_num AND t01.subjects_id = t05.subjects_id AND t05.deleted = 0",
            "INNER JOIN practice_type AS t04 ON t04.id = t05.practice_type_id  AND t04.teacher_type = t01.teacher_type",
            "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
            "AND t04.id = ${practiceTypeId}",
            "</when>",
            "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "AND t01.year_class_id = t03.year_class_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t02.lesson_num = t03.lesson_num ",
            "AND t01.class_type_id = t03.class_type_id ",
            "AND t03.deleted = t02.deleted ",
            "AND t01.teacher_id = t03.teacher_id ",
            "AND t03.practice_type_id = t04.id ",
            "and t03.practice_type_id = t05.practice_type_id",
            "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos; '>",
            "AND t03.practice_type_id = ${practiceTypeId} ",
            "</when>",
            "WHERE t08.id IS NULL ",
            "<when test='trackId!=null and trackId!=&apos;&apos;'>",
                "and t01.teacher_id in(select sourse_id from track_detail where deleted = 0 and track_id IN (#{trackId}) )",
            "</when>",
            ") AS t ON t.teacher_id = t1.teacher_id  ",
            "AND ( find_in_set( t.year_class_id, t1.year_class_id_str ) OR t1.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t1.school_id_str ) OR t1.school_id_str = '' ) ",
            "GROUP BY team_id ",
            "<when test='teamId!=null and teamId!=&apos;&apos;'>",
                ",t.teacher_id",
            "</when>",
            "</script>"})
    public abstract List<PracticePlanStaff> practicePlanStaffTeamList(@Param("practiceTypeId") String practiceTypeId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("lessonNumStart") String lessonNumStart, @Param("lessonNumEnd") String lessonNumEnd, @Param("lessonType") String lessonType, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("teacherName") String teacherName, @Param("classify") String classify, @Param("trackType") String trackType, @Param("teamId") String teamId,@Param("teacherType") Integer teacherType, @Param("trackId") String trackId);


    /**
     * 完成率详情
     */
    @Select({"<script>",
            "SELECT ",
            "t01.*,",
            "getCourseTypeName(t01.class_type_id) as class_type_name,",
            "t04.type as practice_type_id,",
            "t04.`name` as practice_type_name,",
            "getSubjectsName ( t01.subjects_id ) AS subjects_name, ",
            "getDictName ( t01.year_class_id, 14 ) AS year_class_name, ",
            "t02.lesson_num, ",
            "IFNULL( t03.finish_num, 0 ) AS finish_num, ",
            "t03.option_value, ",
            "IFNULL(t03.score,0) as score, ",
            "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date ",
            "FROM ( ",
            "SELECT ",
            "t1.id,t1.school_id,t1.teacher_id,t1.year_class_id,t1.subjects_id,t1.course_type_id AS class_type_id,t1.year_part,t1.quarter_num, ",
            "t1.status,",
            "t1.finish_date,",
            "DATE_FORMAT( t1.created_at, '%Y-%m-%d' ) AS grade_created_at,t2.NAME AS teacher_name, ",
            "getSchoolName ( t1.school_id ) AS school_name, ",
            "getCourseTypeName ( t1.course_type_id ) AS class_type_name,",
            "(SELECT count( id ) FROM student_grade AS stu_g WHERE stu_g.grade_id = t1.id AND stu_g.is_last = 1 AND ((stu_g.reading_status = 1 ) OR ( stu_g.reading_status = 5 AND stu_g.use_course_count > 0 ))) AS stu_grade_count,",
            "1 AS teacher_type ",
            "FROM ",
            "grade AS t1 ",
            "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id AND t1.lesson_type = ${lessonType} ",
            "where",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.deleted = 0 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "AND t1.school_id IN (${schoolId}) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "AND t1.year_class_id IN (${yearClassId}) ",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "AND t1.course_type_id IN (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND t1.subjects_id IN (${subjectsId}) ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "AND t1.teacher_id = ${teacherId} ",
            "</when>",
            "GROUP BY ",
            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id,teacher_type",
            "<if test= 'type == 2 or type == 4 or type == 6'>",
            ",t1.school_id",
            "</if>",
            "UNION ALL",
            "SELECT",
            "t1.id,",
            "t1.school_id,",
            "t1.teacher_id,",
            "t1.year_class_id,",
            "t1.subjects_id,",
            "t1.course_type_id AS class_type_id,",
            "t1.year_part,",
            "t1.quarter_num,",
            "1 as `status`,",
            "NULL AS finish_date,",
            "NULL AS grade_created_at,",
            "t2.NAME AS teacher_name,",
            "getSchoolName ( t1.school_id ) AS school_name,",
            "getCourseTypeName ( t1.course_type_id ) AS class_type_name,",
            "NULL AS stu_grade_count,",
            "t1.teacher_type ",
            "FROM",
            "practice_brush_teacher AS t1",
            "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.deleted = 0 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t1.school_id IN (${schoolId}) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND t1.year_class_id IN (${yearClassId}) ",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND t1.course_type_id IN (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t1.subjects_id IN (${subjectsId}) ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                "AND t1.teacher_id = ${teacherId} ",
            "</when>",
            "GROUP BY ",
            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id,teacher_type",
            "<if test= 'type == 2 or type == 4 or type == 6'>",
            ",t1.school_id",
            "</if>",
            ") AS t01 ",
            "LEFT JOIN rade_exclude_teacher AS t08 ON t01.year_part = t08.year_part  AND t01.quarter_num = t08.quarter_num AND t01.teacher_id = t08.teacher_id AND t08.type = 1",
            "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t02.deleted = 0 ",
            "AND (( t01.teacher_type = 1 AND t01.grade_created_at &lt;= t02.task_start_date AND t01.stu_grade_count > 0 ) OR ( t01.teacher_type = 2 ) or (t01.teacher_type = 1 and stu_grade_count is null)) AND (t01.STATUS &lt;&gt; 2 OR (t01.STATUS = 2 AND ( t01.finish_date &gt;= t02.task_start_date ))) ",
            "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd} ",
            "INNER JOIN practice_subjects_type AS t05 ON t01.year_part = t05.year_part AND t01.quarter_num = t05.quarter_num AND t01.subjects_id = t05.subjects_id AND t05.deleted = 0",
            "INNER JOIN practice_type AS t04 ON t04.id = t05.practice_type_id  AND t04.teacher_type = t01.teacher_type",
            "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
            "AND t04.id = ${practiceTypeId}",
            "</when>",
            "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "AND t01.year_class_id = t03.year_class_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t02.lesson_num = t03.lesson_num ",
            "AND t01.class_type_id = t03.class_type_id ",
            "AND t03.deleted = t02.deleted ",
            "AND t01.teacher_id = t03.teacher_id ",
            "AND t03.practice_type_id = t04.id ",
            "and t03.practice_type_id = t05.practice_type_id",
            "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
            "AND t03.practice_type_id = ${practiceTypeId} ",
            "</when>",
            "WHERE 1=1 ",
            "and t08.id IS NULL ",
            "<when test='detailType!=null and detailType!=&apos;&apos; and detailType==2'>",
                "AND finish_num > 0 ",
            "</when>",
            "<when test='detailType!=null and detailType!=&apos;&apos; and detailType==3'>",
                "AND (finish_num = 0 OR finish_num IS NULL) ",
            "</when>",
            "<when test='detailType!=null and detailType!=&apos;&apos; and detailType==4'>",
                "AND finish_num > 0 ",
                "HAVING plan_add_date BETWEEN '${startDate}' AND '${endDate}' ",
            "</when>",
            "ORDER BY lesson_num, year_class_id ASC ",
            "</script>"})
    public abstract List<PracticePlanStaff> practicePlanStaffDetails(@Param("type") String type, @Param("practiceTypeId") String practiceTypeId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("lessonNumStart") String lessonNumStart, @Param("lessonNumEnd") String lessonNumEnd, @Param("lessonType") String lessonType, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("teacherId") String teacherId, @Param("detailType") String detailType, @Param("teacherType") Integer teacherType);


    /**
     * 完成率战队详情
     */
    @Select({"<script>",
            "SELECT t.* ",
            "FROM",
            "( ",
            "SELECT ",
            "td.*,td.sourse_id as teacher_id ,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_market AS tm ",
            "LEFT JOIN track_detail AS td ON tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = ${classify} ",
            "AND tm.track_type = ${trackType} ",
            "AND tm.`status` = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "and tm.id IN (${teamId}) ",
            "AND td.sourse_id = ${teacherId}",
            "GROUP BY ",
            "tm.id,td.sourse_id ",
            ") AS t1 ",
            "INNER JOIN (",
            "SELECT ",
            "t01.*,t04.type as practice_type_id,t04.`name` as practice_type_name,",
            "getSubjectsName ( t01.subjects_id ) AS subjects_name, ",
            "getDictName ( t01.year_class_id, 14 ) AS year_class_name, ",
            "t02.lesson_num, ",
            "IFNULL( t03.finish_num, 0 ) AS finish_num, ",
            "t03.option_value, ",
            "IFNULL(t03.score,0) as score, ",
            "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date ",
            "FROM ( ",
            "SELECT ",
            "t1.id,t1.school_id,t1.teacher_id,t1.year_class_id,t1.subjects_id,t1.course_type_id AS class_type_id,t1.year_part,t1.quarter_num, ",
            "t1.status,",
            "t1.finish_date,",
            "DATE_FORMAT( t1.created_at, '%Y-%m-%d' ) AS grade_created_at,",
            "t2.NAME AS teacher_name, ",
            "getSchoolName ( t1.school_id ) AS school_name,getCourseTypeName ( t1.course_type_id ) AS class_type_name, ",
            "(SELECT count( id ) FROM student_grade AS stu_g WHERE stu_g.grade_id = t1.id AND stu_g.is_last = 1 AND ((stu_g.reading_status = 1 ) OR ( stu_g.reading_status = 5 AND stu_g.use_course_count > 0 ))) AS stu_grade_count,",
            "1 AS teacher_type ",
            "FROM ",
            "grade AS t1 ",
            "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id AND t1.lesson_type = ${lessonType} ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.deleted = 0 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "AND t1.school_id IN (${schoolId}) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "AND t1.year_class_id IN (${yearClassId}) ",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "AND t1.course_type_id IN (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND t1.subjects_id IN (${subjectsId}) ",
            "</when>",
            "AND t1.teacher_id = ${teacherId} ",
            "GROUP BY ",
            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id",
            "UNION ALL",
            "SELECT",
            "t1.id,",
            "t1.school_id,",
            "t1.teacher_id,",
            "t1.year_class_id,",
            "t1.subjects_id,",
            "t1.course_type_id AS class_type_id,",
            "t1.year_part,",
            "t1.quarter_num,",
            "1 as `status`,",
            "NULL AS finish_date,",
            "NULL AS grade_created_at,",
            "t2.NAME AS teacher_name,",
            "getSchoolName ( t1.school_id ) AS school_name,getCourseTypeName ( t1.course_type_id ) AS class_type_name, ",
            "NULL AS stu_grade_count,",
            "t1.teacher_type ",
            "FROM",
            "practice_brush_teacher AS t1",
            "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.deleted = 0 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND t1.school_id IN (${schoolId}) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
                "AND t1.year_class_id IN (${yearClassId}) ",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                "AND t1.course_type_id IN (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                "AND t1.subjects_id IN (${subjectsId}) ",
            "</when>",
            "AND t1.teacher_id = ${teacherId} ",
            "GROUP BY ",
            "t1.subjects_id,t1.year_class_id,t1.course_type_id,t1.teacher_id,t1.teacher_type",
            ") AS t01 ",
            "LEFT JOIN rade_exclude_teacher AS t08 ON t01.year_part = t08.year_part  AND t01.quarter_num = t08.quarter_num AND t01.teacher_id = t08.teacher_id AND t08.type = 1",
            "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t02.deleted = 0 ",
            "AND (( t01.teacher_type = 1 AND t01.grade_created_at &lt;= t02.task_start_date AND t01.stu_grade_count > 0 ) OR ( t01.teacher_type = 2 ) or (t01.teacher_type = 1 and stu_grade_count is null)) AND (t01.STATUS &lt;&gt; 2 OR (t01.STATUS = 2 AND ( t01.finish_date &gt;= t02.task_start_date ))) ",
            "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd} ",
            "INNER JOIN practice_subjects_type AS t05 ON t01.year_part = t05.year_part AND t01.quarter_num = t05.quarter_num AND t01.subjects_id = t05.subjects_id AND t05.deleted = 0",
            "INNER JOIN practice_type AS t04 ON t04.id = t05.practice_type_id  AND t04.teacher_type = t01.teacher_type",
            "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
            "AND t04.id = ${practiceTypeId}",
            "</when>",
            "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "AND t01.year_class_id = t03.year_class_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t02.lesson_num = t03.lesson_num ",
            "AND t01.class_type_id = t03.class_type_id ",
            "AND t03.deleted = t02.deleted ",
            "AND t01.teacher_id = t03.teacher_id ",
            "AND t03.practice_type_id = t04.id ",
            "and t03.practice_type_id = t05.practice_type_id",
            "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos; '>",
            "AND t03.practice_type_id = ${practiceTypeId} ",
            "</when>",
            "WHERE 1=1 ",
            "and t08.id IS NULL ",
            "<when test='detailType!=null and detailType!=&apos;&apos; and detailType==2'>",
                "AND finish_num > 0 ",
            "</when>",
            "<when test='detailType!=null and detailType!=&apos;&apos; and detailType==3'>",
                "AND (finish_num = 0 OR finish_num IS NULL) ",
            "</when>",
            "<when test='detailType!=null and detailType!=&apos;&apos; and detailType==4'>",
                "AND finish_num > 0 ",
                "HAVING plan_add_date BETWEEN '${startDate}' AND '${endDate}' ",
                "</when>",
            "ORDER BY lesson_num, year_class_id ASC ",
            ") AS t ON t.teacher_id = t1.teacher_id  ",
            "AND ( find_in_set( t.year_class_id, t1.year_class_id_str ) OR t1.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t1.school_id_str ) OR t1.school_id_str = '' ) ",
            "</script>"})
    public abstract List<PracticePlanStaff> practicePlanStaffTeamDetails(@Param("practiceTypeId") String practiceTypeId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("lessonNumStart") String lessonNumStart, @Param("lessonNumEnd") String lessonNumEnd, @Param("lessonType") String lessonType, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("teacherId") String teacherId, @Param("detailType") String detailType, @Param("classify") String classify, @Param("trackType") String trackType, @Param("teamId") String teamId, @Param("teacherType") Integer teacherType);

}
